﻿-- =============================================
-- LoadPolicies.sql
-- =============================================
DECLARE @RC				int
DECLARE @Name			nvarchar(400)
DECLARE @AssertionXml		xml
DECLARE @ID				uniqueidentifier
DECLARE @Description nvarchar(400)
DECLARE @SchemaID uniqueidentifier
DECLARE @schemaObjectType smallint
DECLARE @schemaObjectName nvarchar(256)
DECLARE @schemaObjectXml xml
DECLARE @parentID uniqueidentifier
DECLARE @SchemaObjectID uniqueidentifier
DECLARE @DataEntityID uniqueidentifier
DECLARE @AssertionImplType nvarchar(50)
DECLARE @AssertionTypeID uniqueidentifier
DECLARE @PolicyAssertionID uniqueidentifier
DECLARE @EntityTypeID smallint
DECLARE @PolicyID uniqueidentifier
DECLARE @AssertionOrder smallint

--CREATE SCHEMA
SET @SchemaID = NULL
SET @Name = 'clr-namespace:System.ServiceModel.Configuration;assembly=System.ServiceModel'
SET @Description = ''
EXECUTE @RC = [ServiceEngine].[CreateSchema] 
   @Name
  ,@Description
  ,@SchemaID OUTPUT

--CREATE SCHEMA OBJECT
SET @SchemaObjectID = NULL
SET @schemaObjectType = 3
SET @schemaObjectName = 'ServiceMetadataPublishingElement'
SET @schemaObjectXml = N'
<xs:schema elementFormDefault="qualified" targetNamespace="clr-namespace:System.ServiceModel.Configuration;assembly=System.ServiceModel" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:tns="clr-namespace:System.ServiceModel.Configuration;assembly=System.ServiceModel">
  <xs:element name="ServiceMetadataPublishingElement">
    <xs:complexType>
      <xs:attribute name="HttpGetEnabled" type="xs:boolean" use="optional"></xs:attribute>
      <xs:attribute name="HttpGetUrl" type="xs:string" use="optional"></xs:attribute>
      <xs:attribute name="HttpsGetEnabled" type="xs:boolean" use="optional"></xs:attribute>
      <xs:attribute name="HttpsGetUrl" type="xs:string" use="optional"></xs:attribute>
      <xs:attribute name="PolicyVersion" type="xs:string" use="optional"></xs:attribute>
      <xs:attribute name="ExternalMetadataLocation" type="xs:string" use="optional"></xs:attribute>
    </xs:complexType>
  </xs:element>
</xs:schema>'
SET @parentID=NULL
EXECUTE @RC = [ServiceEngine].[CreateSchemaObject] 
   @SchemaID
  ,@schemaObjectType
  ,@schemaObjectName
  ,@schemaObjectXml
  ,@parentID
  ,@SchemaObjectID OUTPUT

--CREATE DATAENTITY
SET @DataEntityID = null
SET @Name = @schemaObjectName + ':clr-namespace:System.ServiceModel.Configuration;assembly=System.ServiceModel' 
SET @Description = null;
EXECUTE @RC = [ServiceEngine].[CreateDataEntity]
	@Name
	,@Description
	,@DataEntityID OUTPUT

--ASSOCIATE SCHEMAOBJECT TO DATAENTITY
EXECUTE @RC = [ServiceEngine].[AssociateSchemaObjectToDataEntity]
	@DataEntityID
	,@SchemaID
	,@schemaObjectName
	,@schemaObjectType


--CREATE ASSERTION TYPE
SET @AssertionTypeID = NULL
SET @Name = 'Service Metadata'
SET @Description = 'This assertion type defines the service metadata behavior.'
SET @AssertionImplType = null
EXECUTE @RC = [ServiceEngine].[CreateAssertionType] 
   @Name
  ,@Description
  ,@SchemaObjectID
  ,@AssertionImplType
  ,@AssertionTypeID OUTPUT

SET @EntityTypeID = 2 -- endpoint
EXECUTE @RC = [ServiceEngine].[AssignSubjectToAssertionType] 
   @AssertionTypeID
  ,@EntityTypeID

SET @EntityTypeID = 0 -- runtimeserver
EXECUTE @RC = [ServiceEngine].[AssignSubjectToAssertionType] 
   @AssertionTypeID
  ,@EntityTypeID

--CREATE ASSERTION
SET @PolicyAssertionID = NULL
SET @Name = 'Turn OFF Metadata Publishing'
SeT @Description = 'This Assertion can be used at an endpoint to disable metadata publishing.'
SET @AssertionXml = N'
  <wcf:ServiceMetadataPublishingElement HttpGetEnabled="false" HttpGetUrl="/" xmlns:wcf="clr-namespace:System.ServiceModel.Configuration;assembly=System.ServiceModel">
  </wcf:ServiceMetadataPublishingElement>'

EXECUTE @RC = [ServiceEngine].[CreatePolicyAssertion] 
   @Name
  ,@Description
  ,@AssertionTypeID
  ,@AssertionXml
  ,@PolicyAssertionID OUTPUT

--CREATE POLICY
SET @PolicyID = null
SET @Name = 'Turn OFF Metadata Publishing'
SET @Description = 'This policy can be used at an endpoint to disable metadata publishing'
SET @EntityTypeID = 2 --endpoint
EXECUTE @RC = [ServiceEngine].[CreatePolicy] 
   @Name
  ,@EntityTypeID
  ,@Description
  ,@PolicyID OUTPUT


--ASSIGN POLICY ASSERTION TO POLICY
SET @AssertionOrder = 0
EXECUTE @RC = [ServiceEngine].[AssignPolicyAssertionToPolicy] 
   @PolicyID
  ,@PolicyAssertionID
  ,@AssertionOrder


--====================================================================================================
--CREATE SCHEMA OBJECT (SchemaID created above)
SET @SchemaObjectID = null
SET @schemaObjectType = 3
SET @schemaObjectName = 'ServiceDebugElement'
SET @schemaObjectXml = N'
<xs:schema xmlns:tns="clr-namespace:System.ServiceModel.Configuration;assembly=System.ServiceModel" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" targetNamespace="clr-namespace:System.ServiceModel.Configuration;assembly=System.ServiceModel">
  <xs:element name="ServiceDebugElement">
    <xs:complexType>
      <xs:attribute name="IncludeExceptionDetailInFaults" type="xs:boolean" use="required" />
    </xs:complexType>
  </xs:element>
</xs:schema>'
SET @parentID=NULL

EXECUTE @RC = [ServiceEngine].[CreateSchemaObject] 
   @SchemaID
  ,@schemaObjectType
  ,@schemaObjectName
  ,@schemaObjectXml
  ,@parentID
  ,@SchemaObjectID OUTPUT

--CREATE DATAENTITY
SET @DataEntityID = null
SET @Name = @schemaObjectName + ':clr-namespace:System.ServiceModel.Configuration;assembly=System.ServiceModel'
SET @Description = null;
EXECUTE @RC = [ServiceEngine].[CreateDataEntity]
	@Name
	,@Description
	,@DataEntityID OUTPUT

--ASSOCIATE SCHEMAOBJECT TO DATAENTITY
EXECUTE @RC = [ServiceEngine].[AssociateSchemaObjectToDataEntity]
	@DataEntityID
	,@SchemaID
	,@schemaObjectName
	,@schemaObjectType
	
--CREATE ASSERTION TYPE
SET @AssertionTypeID = NULL
SET @Name = 'Service Debug'
SET @Description = 'This assertion type defines the service debug behavior.'
SET @AssertionImplType = null
EXECUTE @RC = [ServiceEngine].[CreateAssertionType] 
   @Name
  ,@Description
  ,@SchemaObjectID
  ,@AssertionImplType
  ,@AssertionTypeID OUTPUT

SET @EntityTypeID = 2 -- endpoint
EXECUTE @RC = [ServiceEngine].[AssignSubjectToAssertionType] 
   @AssertionTypeID
  ,@EntityTypeID

SET @EntityTypeID = 0 -- runtimeserver
EXECUTE @RC = [ServiceEngine].[AssignSubjectToAssertionType] 
   @AssertionTypeID
  ,@EntityTypeID


--CREATE ASSERTION
SET @PolicyAssertionID = NULL
SET @Name = 'Turn ON Service Debug'
SeT @Description = 'This Assertion can be used at an endpoint to enabled the flow of managed exception information to a consumer.'
SET @AssertionXml = N'
<ServiceDebugElement xmlns="clr-namespace:System.ServiceModel.Configuration;assembly=System.ServiceModel" IncludeExceptionDetailInFaults="true">
</ServiceDebugElement>'

EXECUTE @RC = [ServiceEngine].[CreatePolicyAssertion] 
   @Name
  ,@Description
  ,@AssertionTypeID
  ,@AssertionXml
  ,@PolicyAssertionID OUTPUT

--CREATE POLICY
SET @PolicyID = null
SET @Name = 'Turn ON Service Debug'
SET @Description = 'This policy can be used at an endpoint to enable flowing of managed exception information'
SET @EntityTypeID = 2 --endpoint
EXECUTE @RC = [ServiceEngine].[CreatePolicy] 
   @Name
  ,@EntityTypeID
  ,@Description
  ,@PolicyID OUTPUT


--ASSIGN POLICY ASSERTION TO POLICY
SET @AssertionOrder = 0
EXECUTE @RC = [ServiceEngine].[AssignPolicyAssertionToPolicy] 
   @PolicyID
  ,@PolicyAssertionID
  ,@AssertionOrder

--====================================================================================================

--CREATE SCHEMA
SET @SchemaID = NULL
SET @Name = 'clr-namespace:Microsoft.MSE.Behaviors.XslTransformation;assembly=Microsoft.MSE.Behaviors.XslTransformation'
SET @Description = ''
EXECUTE @RC = [ServiceEngine].[CreateSchema] 
   @Name
  ,@Description
  ,@SchemaID OUTPUT

--CREATE SCHEMA OBJECT
SET @SchemaObjectID = NULL
SET @schemaObjectType = 3
SET @schemaObjectName = 'XslResponseTransformationBehaviorExtensionElement'
SET @schemaObjectXml = N'
<xs:schema elementFormDefault="qualified" targetNamespace="clr-namespace:Microsoft.MSE.Behaviors.XslTransformation;assembly=Microsoft.MSE.Behaviors.XslTransformation" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:tns="clr-namespace:Microsoft.MSE.Behaviors.XslTransformation;assembly=Microsoft.MSE.Behaviors.XslTransformation">
	<xs:element name="XslResponseTransformationBehaviorExtensionElement">
		<xs:complexType>
			<xs:sequence>
				<xs:element name="XslTransformationInformation">
					<xs:complexType>
						<xs:sequence>
							<xs:element name="XslTransformationInformation.Transform" type="xs:string" />
						</xs:sequence>
					</xs:complexType>
				</xs:element>
			</xs:sequence>
		</xs:complexType>
	</xs:element>
</xs:schema>'
SET @parentID=NULL
EXECUTE @RC = [ServiceEngine].[CreateSchemaObject] 
   @SchemaID
  ,@schemaObjectType
  ,@schemaObjectName
  ,@schemaObjectXml
  ,@parentID
  ,@SchemaObjectID OUTPUT

----CREATE DATAENTITY
SET @DataEntityID = null
SET @Name = @schemaObjectName + ':clr-namespace:Microsoft.MSE.Behaviors.XslTransformation;assembly=Microsoft.MSE.Behaviors.XslTransformation'
SET @Description = null;
EXECUTE @RC = [ServiceEngine].[CreateDataEntity]
	@Name
	,@Description
	,@DataEntityID OUTPUT

----ASSOCIATE SCHEMAOBJECT TO DATAENTITY
EXECUTE @RC = [ServiceEngine].[AssociateSchemaObjectToDataEntity]
	@DataEntityID
	,@SchemaID
	,@schemaObjectName
	,@schemaObjectType

----CREATE ASSERTION TYPE
SET @AssertionTypeID = NULL
SET @Name = 'Xsl Response Transform'
SET @Description = 'Used to transform response messages with Xslt.'
SET @AssertionImplType = null
EXECUTE @RC = [ServiceEngine].[CreateAssertionType] 
   @Name
  ,@Description
  ,@SchemaObjectID
  ,@AssertionImplType
  ,@AssertionTypeID OUTPUT

SET @EntityTypeID = 21 -- OpVer
EXECUTE @RC = [ServiceEngine].[AssignSubjectToAssertionType] 
   @AssertionTypeID
  ,@EntityTypeID

SET @EntityTypeID = 16 -- Resource
EXECUTE @RC = [ServiceEngine].[AssignSubjectToAssertionType] 
   @AssertionTypeID
  ,@EntityTypeID

SET @EntityTypeID = 12 -- System
EXECUTE @RC = [ServiceEngine].[AssignSubjectToAssertionType] 
   @AssertionTypeID
  ,@EntityTypeID

SET @EntityTypeID = 14 -- SystemInstance
EXECUTE @RC = [ServiceEngine].[AssignSubjectToAssertionType] 
   @AssertionTypeID
  ,@EntityTypeID


--CREATE SCHEMA OBJECT
SET @SchemaObjectID = NULL
SET @schemaObjectType = 3
SET @schemaObjectName = 'XslRequestTransformationBehaviorExtensionElement'
SET @schemaObjectXml = N'
<xs:schema elementFormDefault="qualified" targetNamespace="clr-namespace:Microsoft.MSE.Behaviors.XslTransformation;assembly=Microsoft.MSE.Behaviors.XslTransformation" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:tns="clr-namespace:Microsoft.MSE.Behaviors.XslTransformation;assembly=Microsoft.MSE.Behaviors.XslTransformation">
	<xs:element name="XslRequestTransformationBehaviorExtensionElement">
		<xs:complexType>
			<xs:sequence>
				<xs:element name="XslTransformationInformation">
					<xs:complexType>
						<xs:sequence>
							<xs:element name="XslTransformationInformation.Transform" type="xs:string" />
						</xs:sequence>
					</xs:complexType>
				</xs:element>
			</xs:sequence>
		</xs:complexType>
	</xs:element>
</xs:schema>'
SET @parentID=NULL
EXECUTE @RC = [ServiceEngine].[CreateSchemaObject] 
   @SchemaID
  ,@schemaObjectType
  ,@schemaObjectName
  ,@schemaObjectXml
  ,@parentID
  ,@SchemaObjectID OUTPUT

----CREATE DATAENTITY
SET @DataEntityID = null
SET @Name = @schemaObjectName + ':clr-namespace:Microsoft.MSE.Behaviors.XslTransformation;assembly=Microsoft.MSE.Behaviors.XslTransformation'
SET @Description = null;
EXECUTE @RC = [ServiceEngine].[CreateDataEntity]
	@Name
	,@Description
	,@DataEntityID OUTPUT

----ASSOCIATE SCHEMAOBJECT TO DATAENTITY
EXECUTE @RC = [ServiceEngine].[AssociateSchemaObjectToDataEntity]
	@DataEntityID
	,@SchemaID
	,@schemaObjectName
	,@schemaObjectType

----CREATE ASSERTION TYPE
SET @AssertionTypeID = NULL
SET @Name = 'Xsl Request Transform'
SET @Description = 'Used to transform request messages with Xslt.'
SET @AssertionImplType = null
EXECUTE @RC = [ServiceEngine].[CreateAssertionType] 
   @Name
  ,@Description
  ,@SchemaObjectID
  ,@AssertionImplType
  ,@AssertionTypeID OUTPUT

SET @EntityTypeID = 21 -- OpVer
EXECUTE @RC = [ServiceEngine].[AssignSubjectToAssertionType] 
   @AssertionTypeID
  ,@EntityTypeID

SET @EntityTypeID = 16 -- Resource
EXECUTE @RC = [ServiceEngine].[AssignSubjectToAssertionType] 
   @AssertionTypeID
  ,@EntityTypeID

SET @EntityTypeID = 12 -- System
EXECUTE @RC = [ServiceEngine].[AssignSubjectToAssertionType] 
   @AssertionTypeID
  ,@EntityTypeID

SET @EntityTypeID = 14 -- SystemInstance
EXECUTE @RC = [ServiceEngine].[AssignSubjectToAssertionType] 
   @AssertionTypeID
  ,@EntityTypeID


--====================================================================================================

--CREATE SCHEMA
--SET @SchemaID = NULL
--SET @Name = 'clr-namespace:Microsoft.MSE.Behaviors.RegExContentFilter;assembly=Microsoft.MSE.Behaviors.RegExContentFilter'
--SET @Description = ''
--EXECUTE @RC = [ServiceEngine].[CreateSchema] 
   --@Name
  --,@Description
  --,@SchemaID OUTPUT

----CREATE SCHEMA OBJECT
--SET @SchemaObjectID = NULL
--SET @schemaObjectType = 3
--SET @schemaObjectName = 'RegExContentBehaviorExtensionElement'
--SET @schemaObjectXml = N'
--<xs:schema elementFormDefault="qualified" targetNamespace="clr-namespace:Microsoft.MSE.Behaviors.RegExContentFilter;assembly=Microsoft.MSE.Behaviors.RegExContentFilter" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:tns="clr-namespace:Microsoft.MSE.Behaviors.RegExContentFilter;assembly=Microsoft.MSE.Behaviors.RegExContentFilter">
  --<xs:element name="RegExContentBehaviorExtensionElement">
    --<xs:complexType>
      --<xs:sequence>
        --<xs:element name="RegPatternItem">
          --<xs:complexType>
            --<xs:sequence>
              --<xs:element name="RegPatternItem.Pattern" type="xs:string"></xs:element>
              --<xs:element name="RegPatternItem.Replacement" type="xs:string"></xs:element>
            --</xs:sequence>
          --</xs:complexType>
        --</xs:element>
      --</xs:sequence>
    --</xs:complexType>
  --</xs:element>
--</xs:schema>'
--SET @parentID=NULL
--EXECUTE @RC = [ServiceEngine].[CreateSchemaObject] 
   --@SchemaID
  --,@schemaObjectType
  --,@schemaObjectName
  --,@schemaObjectXml
  --,@parentID
  --,@SchemaObjectID OUTPUT

----CREATE DATAENTITY
--SET @DataEntityID = null
--SET @Name = @schemaObjectName + ':clr-namespace:Microsoft.MSE.Behaviors.RegExContentFilter;assembly=Microsoft.MSE.Behaviors.RegExContentFilter'
--SET @Description = null;
--EXECUTE @RC = [ServiceEngine].[CreateDataEntity]
	--@Name
	--,@Description
	--,@DataEntityID OUTPUT

----ASSOCIATE SCHEMAOBJECT TO DATAENTITY
--EXECUTE @RC = [ServiceEngine].[AssociateSchemaObjectToDataEntity]
	--@DataEntityID
	--,@SchemaID
	--,@schemaObjectName
	--,@schemaObjectType

----CREATE ASSERTION TYPE
--SET @AssertionTypeID = NULL
--SET @Name = 'RegEx Filter'
--SET @Description = 'This assertion type can be used to enable Regular Expression filtering across messages.'
--SET @AssertionImplType = null
--EXECUTE @RC = [ServiceEngine].[CreateAssertionType] 
   --@Name
  --,@Description
  --,@SchemaObjectID
  --,@AssertionImplType
  --,@AssertionTypeID OUTPUT

--SET @EntityTypeID = 2 -- endpoint
--EXECUTE @RC = [ServiceEngine].[AssignSubjectToAssertionType] 
   --@AssertionTypeID
  --,@EntityTypeID


----CREATE ASSERTION
--SET @PolicyAssertionID = NULL
--SET @Name = 'RegEx Replace AddDoublesResult'
--SeT @Description = 'This assertion replaces the AddDoublesResult element with a fixed string.'
--SET @AssertionXml = N'
--<regEx:RegExContentBehaviorExtensionElement xmlns:regEx="clr-namespace:Microsoft.MSE.Behaviors.RegExContentFilter;assembly=Microsoft.MSE.Behaviors.RegExContentFilter">
  --<regEx:RegPatternItem>
    --<regEx:RegPatternItem.Pattern>AddDoublesResult</regEx:RegPatternItem.Pattern>
    --<regEx:RegPatternItem.Replacement>sum</regEx:RegPatternItem.Replacement>
  --</regEx:RegPatternItem>
--</regEx:RegExContentBehaviorExtensionElement>'

--EXECUTE @RC = [ServiceEngine].[CreatePolicyAssertion] 
   --@Name
  --,@Description
  --,@AssertionTypeID
  --,@AssertionXml
  --,@PolicyAssertionID OUTPUT

----CREATE POLICY
--SET @PolicyID = null
--SET @Name = 'RegEx Replace AddDoublesResult'
--SET @Description = 'This policy replaces the AddDoublesResult element with a fixed string.'
--SET @EntityTypeID = 2 --endpoint
--EXECUTE @RC = [ServiceEngine].[CreatePolicy] 
   --@Name
  --,@EntityTypeID
  --,@Description
  --,@PolicyID OUTPUT


----ASSIGN POLICY ASSERTION TO POLICY
--SET @AssertionOrder = 0
--EXECUTE @RC = [ServiceEngine].[AssignPolicyAssertionToPolicy] 
   --@PolicyID
  --,@PolicyAssertionID
  --,@AssertionOrder

GO